﻿using FOModels.MobileBanking;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess.MobileBanking
{
    public class MobileBankingDAL
    {
        private SQLDataAccess db;
        public MobileBankingDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getMobileBankingList(int langid)
        {
            return db.exeSelect("select * from MobileBanking ca join MobileBankingLang cl on ca.MobileBankingID = cl.MobileBankingID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where co.Active = 1 and cl.LangID = " + langid + " and cla.LangID = " + langid + " order by ca.MobileBankingID desc");
        }
        public int InsertMobileBanking(MobileBankingModels models)
        {
            return db.exeUpdate("insert into MobileBanking(MobileBankingName,CompanyID,Cost,Interest,Display,UserPost) values (N'" + models.MobileBankingName + "'," + models.CompanyID + "," + models.Cost + "," + models.Interest + ",'" + models.Display + "','" + models.UserPost + "') ");
        }
        public int InsertMobileBankingLang(MobileBankingModels models, int langid)
        {
            return db.exeUpdate("insert into MobileBankingLang values (" + models.MobileBankingID + "," + langid + ",N'" + models.MobileBankingName + "',N'" + models.BenefitContent + "',N'" + models.FurtureContent + "',N'" + models.Description + "',N'" + models.MoreBenefit + "',N'" + models.MoreFee + "',N'" + models.MoreInfo + "')");
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("Select TOP 1 * from MobileBanking order by MobileBankingID desc");
        }
        public DataTable GetAllToManager(int langid)
        {
            return db.exeSelect("Select * from MobileBanking sa join MobileBankingLang sl on sa.MobileBankingID = sl.MobileBankingID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join Language la on sl.LangID = la.ID where cl.LangID = " + langid + " and sl.LangID = " + langid + "");
        }

        public int UpdateMobileBankingLang(MobileBankingModels models, int langid)
        {
            return db.exeUpdate("update MobileBankingLang set MobileBankingName = N'" + models.MobileBankingName + "',BenefitContent=N'" + models.BenefitContent + "',FurtureContent=N'" + models.FurtureContent + "',Description=N'" + models.Description + "',MoreBenefit = N'" + models.MoreBenefit + "',MoreFee = N'" + models.MoreFee + "',MoreInfo = N'" + models.MoreInfo + "' where MobileBankingID = " + models.MobileBankingID + " and LangID =" + langid + "");
        }
        public int UpdateMobileBanking(MobileBankingModels models)
        {
            return db.exeUpdate("update MobileBanking set CompanyID= " + models.CompanyID + ",Cost=" + models.Cost + ",Interest=" + models.Interest + ",Display = '" + models.Display + "',UserUpdate = '" + models.UserUpdate + "',DateUpdate = '" + models.DateUpdate + "' where MobileBankingID = " + models.MobileBankingID + "");
        }
        public int DeleteMobileBanking(int MobileBankingID)
        {
            return db.exeUpdate("Delete from MobileBanking where MobileBankingID = " + MobileBankingID + "");
        }
        public int DeleteMobileBankingLang(int MobileBankingID)
        {
            return db.exeUpdate("Delete from MobileBankingLang where MobileBankingID = " + MobileBankingID + "");
        }

        public DataTable GetMobileBankingByIDToManager(int MobileBankingID, int langid)
        {
            return db.exeSelect("select * from MobileBanking ca join MobileBankingLang cl on ca.MobileBankingID = cl.MobileBankingID join Company co on ca.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID= coml.CompanyID where  ca.MobileBankingID = " + MobileBankingID + " and coml.LangID=" + langid + "  and cl.LangID=" + langid + "");
        }
        public DataTable GetMobileBankingByID(int MobileBankingID, int langid)
        {
            return db.exeSelect("select * from MobileBanking ca join MobileBankingLang cl on ca.MobileBankingID = cl.MobileBankingID join Company co on ca.CompanyID = co.CompanyID join CompanyLang coml on co.CompanyID= coml.CompanyID where co.Active = 1 and  ca.MobileBankingID = " + MobileBankingID + " and coml.LangID=" + langid + "  and cl.LangID=" + langid + "");
        }
        public int DeleteMobileBankingApply(int MobileBankingApplyID)
        {
            return db.exeUpdate("Delete from MobileBankingApply where MobileBankingApplyID = " + MobileBankingApplyID + "");
        }
        public int UpdateStatus(MobileBankingApplyModels card)
        {
            return db.exeUpdate("update MobileBankingApply set StatusID= " + card.StatusID + " where MobileBankingApplyID = " + card.MobileBankingApplyID + "");
        }
        public int MobileBankingApply(int MobileBankingID, MobileBankingApplyModels app)
        {
            return db.exeUpdate("insert into MobileBankingApply(MobileBankingID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,CurrentAccount,SavingsDeposits,CreditCard,HomeLoan,CarLoan,PersonalLoan,BusinessLoan,City,ContactTime,StatusID) values (" + MobileBankingID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "','" + app.CurrentAccount + "','" + app.SavingsDeposits + "','" + app.CreditCard + "','" + app.HomeLoan + "','" + app.CarLoan + "','" + app.PersonalLoan + "','" + app.BusinessLoan + "',N'" + app.City + "',N'" + app.ContactTime + "'," + app.StatusID + ")");
        }
        public DataTable getcontractbyid(int cardapplyid)
        {
            return db.exeSelect("select * from MobileBankingApply ca join MobileBanking c on ca.MobileBankingID = c.MobileBankingID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.MobileBankingApplyID = " + cardapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontract()
        {
            return db.exeSelect("select * from MobileBankingApply ca join [MobileBanking] c on ca.MobileBankingID = c.MobileBankingID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1");
        }
        public DataTable getcontractbystatus(int statusid)
        {
            return db.exeSelect("select * from MobileBankingApply ca join [MobileBanking] c on ca.MobileBankingID = c.MobileBankingID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1 and ca.StatusID = " + statusid + "");
        }
        public DataTable getcontractbystatuscount(int statusid)
        {
            return db.exeSelect("select * from MobileBankingApply ca join [MobileBanking] c on ca.MobileBankingID = c.MobileBankingID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.StatusID = " + statusid + " and cl.LangID = 1");
        }
        public DataTable getMobileBankingbyProvider(int CompanyID, int langid)
        {
            return db.exeSelect("select * from MobileBanking ca join MobileBankingLang cl on ca.MobileBankingID = cl.MobileBankingID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cla on co.CompanyID = cla.CompanyID where  co.Active = 1 and cl.LangID = " + langid + " and cla.LangID = " + langid + " and ca.CompanyID= " + CompanyID + "");
        }

    }
}
